IS [NOT] JSON

Purpose

This tests whether an expression is a valid JSON string that follows the syntax rules on JSON.org.

Syntax

json_predicate::=

json_predicate_1

json_predicate_2

json_input_clause::=

json_input_clause

Usage Notes

  • A valid JSON string is either a JSON array, a JSON object, or a JSON scalar. In each case, it has to be valid according to the syntax rules on JSON.org.
  • IS JSON is equivalent to IS JSON VALUE and returns true for a valid JSON array, a valid JSON object, or a valid JSON scalar.
  • IS JSON ARRAY is only true if the input expression is a valid JSON array according to the syntax rules on JSON.org.
  • IS JSON OBJECT is only true if the input expression is a valid JSON object according to the syntax rules on JSON.org.
  • IS JSON SCALAR is only true if the input expression is a valid JSON scalar according to the syntax rules on JSON.org.
  • A JSON object may have non-unique keys. For example in the JSON string '{"name" : "Ann", "name" : "John"}', 'name' is a non-unique key.
  • If WITH UNIQUE [KEYS] is set, IS JSON returns false for valid JSON objects with non-unique keys. WITHOUT UNIQUE [KEYS] is the default.
Example
CREATE OR REPLACE TABLE json_input(id INT, json VARCHAR(2000000));
INSERT INTO json_input VALUES(1, NULL);
INSERT INTO json_input VALUES(2, '{"name" : "John"}');
INSERT INTO json_input VALUES(3, '{"name" : {"forename" : "John", "name" : "Doe"}}');
INSERT INTO json_input VALUES(4, '{"name" : "John", "name" : "Ann"}');
INSERT INTO json_input VALUES(5, '[{"name" : "John"}]');
INSERT INTO json_input VALUES(6, 'true');
INSERT INTO json_input VALUES(7, 'false');
INSERT INTO json_input VALUES(8, 'null');
INSERT INTO json_input VALUES(9, '42');
INSERT INTO json_input VALUES(10, '1.0');
INSERT INTO json_input VALUES(11, '"Exasol"');
INSERT INTO json_input VALUES(12, '{name : "John"}');

SELECT id, json, json IS JSON as IS_JSON, json IS NOT JSON as IS_NOT_JSON,
       json IS JSON WITH UNIQUE KEYS as IS_JSON_WITH_UNIQUE_KEYS,
       json IS JSON VALUE as "JSON VALUE", json IS JSON ARRAY as JSON_ARRAY,
       json IS JSON OBJECT as JSON_OBJECT, json IS JSON SCALAR as JSON_SCALAR
FROM json_input
ORDER BY id;
Results
ID JSON IS_JSON IS_NOT_JSON IS_JSON_WITH_UNIQUE_KEYS JSON VALUE JSON_ARRAY JSON_OBJECT JSON_SCALAR
1 NULL NULL NULL NULL NULL NULL NULL NULL
2 {"name" : "John"} true false true true false true false
3 {"name" : {"forename" : "John", "name" : "Doe"}} true false true true false true false
4 {"name" : "John", "name" : "Ann"} true false false true false true false
5 [{"name" : "John"}] true false true true true false false
6 true true false true true false false true
7 false true false true true false false true
8 null true false true true false false true
9 42 true false true true false false true
10 1.0 true false true true false false true
11 Exasol true false true true false false true
12 {name: "John"} false true false false false false false